﻿Imports MySql.Data.MySqlClient
Imports System.Threading
Imports Microsoft.Office.Interop
Public Class frmview_ctndamage
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim pathExcel As String
    Private Sub frmview_ctndamage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select  CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where CTNSTAT= '0' order by CTNMAINID ASC;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNMAINID"))
                    .SubItems.Add(mySqlReader("CTNSTRING"))
                    .SubItems.Add(mySqlReader("CTNAGENT"))
                    .SubItems.Add(mySqlReader("CTNCONSI"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(mySqlReader("CTNSIZE"))


                    .SubItems.Add("N" + Format(mySqlReader("CTNVOYN"), "000"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


        txt_count.Text = ListView1.Items.Count


    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            pathExcel = FolderBrowserDialog1.SelectedPath
            excelReport()
        End If

    End Sub
    Private Sub excelReport()
        Dim count_Row As Integer = 5
        Dim count_Row2 As Integer = 9


        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Rows("3:3").rowheight = 20


        With (excelsheets)
            .Range("A1:Q900").Font.Name = "Angsana New"

            .Range("A2:Q900").Font.Size = 14
            Dim CheckIndex As Integer
            Dim i As Integer
            Dim CheckData As Boolean
            CheckData = False
            CheckIndex = ListView1.Items.Count
            Dim J As Integer
            For J = 7 To 10
                .Range("A4").Borders(J).Weight = 2 ' xlThin
                .Range("B4").Borders(J).Weight = 2 ' xlThin
                .Range("C4").Borders(J).Weight = 2 ' xlThin
                .Range("D4").Borders(J).Weight = 2 ' xlThin
                .Range("E4").Borders(J).Weight = 2 ' xlThin
                .Range("F4").Borders(J).Weight = 2 ' xlThin


            Next
            With .Range("A2:F2")
                .Merge()


                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "YKP PORT CONTAINER DAMAGE"
                .Font.Size = 16
                ''.ColumnWidth = 20
            End With
            With .Range("A3:F3")
                .Merge()
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Report Container DAMAGE"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("A4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Container No."
                .ColumnWidth = 15
            End With
            With .Range("B4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Agent Line"
                .ColumnWidth = 12
            End With
            With .Range("C4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Consignee"
                .Font.Size = 14

            End With

            With .Range("D4")


                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Date Recieve"
                .Font.Size = 14
                .ColumnWidth = 20
            End With
            With .Range("E4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Size"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("F4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "VOYN"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With

            For i = 0 To CheckIndex - 1


                With .Range("A" + count_Row.ToString)
                    .Value = ListView1.Items(i).SubItems(1).Text()
                End With

                With .Range("B" + count_Row.ToString)
                    .Value = ListView1.Items(i).SubItems(2).Text()
                End With
                With .Range("C" + count_Row.ToString)
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Value = ListView1.Items(i).SubItems(3).Text()
                End With
                With .Range("D" + count_Row.ToString)
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    .Value = ListView1.Items(i).SubItems(4).Text()
                End With
                With .Range("E" + count_Row.ToString)
                    .Value = ListView1.Items(i).SubItems(5).Text()
                End With
                With .Range("F" + count_Row.ToString)
                    .Value = ListView1.Items(i).SubItems(6).Text()
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                End With

                count_Row += 1
            Next
            For i = 5 To count_Row
                .Range("B" & i.ToString() & ":B" & i.ToString()).Borders(7).Weight = 2
                .Range("A" & i.ToString() & ":A" & i.ToString()).Borders(7).Weight = 2
                .Range("C" & i.ToString() & ":C" & i.ToString()).Borders(7).Weight = 2
                .Range("D" & i.ToString() & ":D" & i.ToString()).Borders(7).Weight = 2
                .Range("E" & i.ToString() & ":E" & i.ToString()).Borders(7).Weight = 2
                .Range("F" & i.ToString() & ":F" & i.ToString()).Borders(7).Weight = 2
                .Range("G" & i.ToString() & ":G" & i.ToString()).Borders(7).Weight = 2


            Next
            count_Row += 1
            With .Range("A" + count_Row.ToString + ":F" + count_Row.ToString)
                .Borders(8).Weight = 2
            End With

        End With
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportContainerDamage" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox("NOT SAVED")
        End Try


    End Sub

    Private Sub ButtonItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem1.Click

    End Sub
End Class